/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import DTO.album.AlbumType;
import DTO.album.Albums;
import DTO.album.ArtistType;
import DTO.album.ArtistsType;
import DTO.album.CommentsType;
import DTO.album.SongsType;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.GregorianCalendar;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.datatype.DatatypeConfigurationException;
import javax.xml.datatype.DatatypeFactory;
import javax.xml.datatype.XMLGregorianCalendar;

/**
 *
 * @author DinhNP
 */
public class AlbumDAO {

    public DTO.album.Albums getAllAlbums() {

        Connection con = null;
        PreparedStatement stm = null;
        DTO.album.Albums albumList = null;

        try {
            con = DAO.DBConnection.getConnection();
            stm = con.prepareStatement("SELECT Albums.*, Artists.ArtistID, Artists.ArtistName "
                    + "FROM Albums "
                    + "INNER JOIN Artists "
                    + "ON Albums.ArtistID = Artists.ArtistID");

            ResultSet rs = stm.executeQuery();
            albumList = new Albums();

            while (rs.next()) {
                AlbumType temp = new AlbumType();

                temp.setAlbumID(rs.getBigDecimal("AlbumID").toBigInteger());
                temp.setAlbumTitle(rs.getString("AlbumTitle"));
                temp.setYear(rs.getBigDecimal("Year").toBigInteger());
                temp.setArtistID(rs.getBigDecimal("ArtistID").toBigInteger());
                temp.setArtistName(rs.getString("ArtistName"));
                temp.setVariousArtist(rs.getBoolean("VariousArtist"));
                temp.setAlbumDescription(rs.getString("AlbumDescription"));

                Timestamp time = rs.getTimestamp("TotalTime");
                GregorianCalendar gcal = new GregorianCalendar();
                gcal.setTime(time);
                XMLGregorianCalendar xgcal = DatatypeFactory.newInstance().newXMLGregorianCalendar(gcal);
                temp.setTotalTime(xgcal);

                temp.setTracksCount(rs.getBigDecimal("TracksCount").toBigInteger());
                temp.setAlbumCoverSRC(rs.getString("AlbumCoverSRC"));
                temp.setAlbumViews(rs.getBigDecimal("AlbumViews").toBigInteger());

                time = rs.getTimestamp("AlbumDateCreated");
                gcal = new GregorianCalendar();
                gcal.setTime(time);
                xgcal = DatatypeFactory.newInstance().newXMLGregorianCalendar(gcal);
                temp.setAlbumDateCreated(xgcal);

                temp.setSongs(getTrackListByAlbumID(temp.getAlbumID().intValue()));
                temp.setComments(getCommentListByAlbumID(temp.getAlbumID().intValue()));

                albumList.getAlbum().add(temp);
            }
            return albumList;
        } catch (DatatypeConfigurationException ex) {
            Logger.getLogger(AlbumDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    public CommentsType getCommentListByAlbumID(int albumID) {

        Connection con = null;
        PreparedStatement stm = null;
        CommentsType commentList = null;

        try {
            con = DAO.DBConnection.getConnection();
            stm = con.prepareStatement("SELECT Albums.AlbumID, Comment.*"
                    + "FROM Comment "
                    + "INNER JOIN Albums "
                    + "ON Comment.LocationID = Albums.AlbumID "
                    + "WHERE Comment.CommentType = 'album'");

            ResultSet rs = stm.executeQuery();
            commentList = new CommentsType();

            while (rs.next()) {
                if (rs.getInt("AlbumID") == albumID) {
                    DTO.album.CommentType temp = new DTO.album.CommentType();

                    temp.setCommentID(rs.getBigDecimal("CommentID").toBigInteger());
                    temp.setUsername(rs.getString("Username"));
                    temp.setCommentContent(rs.getString("CommentContent"));

                    Date d = rs.getDate("DateCreated");
                    GregorianCalendar gcal = new GregorianCalendar();
                    gcal.setTime(d);
                    XMLGregorianCalendar xgcal = DatatypeFactory.newInstance().newXMLGregorianCalendar(gcal);
                    temp.setDateCreated(xgcal);

                    temp.setCommentType(rs.getString("CommentType"));
                    temp.setLocationID(rs.getBigDecimal("LocationID").toBigInteger());

                    commentList.getComment().add(temp);
                }

            }
            return commentList;
        } catch (DatatypeConfigurationException ex) {
            Logger.getLogger(AlbumDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    public SongsType getTrackListByAlbumID(int albumID) {

        Connection con = null;
        PreparedStatement stm = null;
        SongsType trackList = null;

        try {
            con = DAO.DBConnection.getConnection();
            stm = con.prepareStatement("SELECT Tracks.AlbumID, Tracks.TrackNumber, Songs.* "
                    + "FROM Tracks "
                    + "INNER JOIN Songs "
                    + "ON Tracks.SongID = Songs.SongID");

            ResultSet rs = stm.executeQuery();
            trackList = new SongsType();

            while (rs.next()) {
                if (rs.getInt("AlbumID") == albumID) {
                    DTO.album.SongType temp = new DTO.album.SongType();

                    temp.setSongID(rs.getBigDecimal("SongID").toBigInteger());
                    temp.setSongTitle(rs.getString("SongTitle"));
                    temp.setSongSRC(rs.getString("SongSRC"));
                    temp.setLyrics(rs.getString("Lyrics"));
                    temp.setIsMV(rs.getBoolean("IsMV"));
                    temp.setArtists(getArtistListBySongID(temp.getSongID().intValue()));
                    temp.setOrderNumber(rs.getBigDecimal("TrackNumber").toBigInteger());
                    trackList.getSong().add(temp);
                }

            }
            return trackList;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    public ArtistsType getArtistListBySongID(int songId) {

        Connection con = null;
        PreparedStatement stm = null;
        ArtistsType artistList = null;

        try {
            con = DAO.DBConnection.getConnection();
            stm = con.prepareStatement("SELECT sd.*, Artists.ArtistName "
                    + "FROM SongDetailsByArtist AS sd "
                    + "INNER JOIN Artists "
                    + "ON sd.ArtistID = Artists.ArtistID");

            ResultSet rs = stm.executeQuery();
            artistList = new ArtistsType();

            while (rs.next()) {
                if (rs.getInt("SongID") == songId) {
                    ArtistType temp = new ArtistType();
                    temp.setArtistID(BigInteger.valueOf(rs.getInt("ArtistID")));
                    temp.setArtistName(rs.getString("ArtistName"));
                    artistList.getArtist().add(temp);
                }
            }
            return artistList;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }

    }

    public boolean add(DTO.album.AlbumType album) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = DAO.DBConnection.getConnection();
            stm = con.prepareStatement("INSERT INTO [Albums](AlbumTitle,Year,"
                    + "ArtistID,VariousArtist,AlbumDescription,TotalTime,TracksCount,"
                    + "AlbumCoverSRC,AlbumViews,AlbumDateCreated) VALUES(?,?,?,?,?,?,?,?,?,?)");
            stm.setString(1, album.getAlbumTitle());
            stm.setInt(2, album.getYear().intValue());
            stm.setInt(3, album.getArtistID().intValue());
            stm.setBoolean(4, album.isVariousArtist());
            stm.setString(5, album.getAlbumDescription());
            stm.setTime(6, Time.valueOf(album.getTotalTime().toString()));
            stm.setInt(7, album.getTracksCount().intValue());
            stm.setString(8, album.getAlbumCoverSRC());
            stm.setInt(9, album.getAlbumViews().intValue());
            stm.setDate(10, java.sql.Date.valueOf(album.getAlbumDateCreated().toString()));
            stm.executeUpdate();
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    public boolean delete(int albumID) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = DAO.DBConnection.getConnection();
            stm = con.prepareStatement("DELETE FROM [Albums] WHERE AlbumID=?");
            stm.setInt(1, albumID);
            stm.executeUpdate();
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    public boolean update(DTO.album.AlbumType album) {
        Connection con = null;
        PreparedStatement stm = null;
        try {
            con = DAO.DBConnection.getConnection();
            stm = con.prepareStatement("UPDATE [Albums] SET Year =?, "
                    + "ArtistID=?,VariousArtist=?,AlbumDescription=?,TotalTime=?,TracksCount=?,"
                    + "AlbumCoverSRC=?,AlbumViews=?,AlbumDateCreated=?");
            stm.setString(1, album.getAlbumTitle());
            stm.setInt(2, album.getYear().intValue());
            stm.setInt(3, album.getArtistID().intValue());
            stm.setBoolean(4, album.isVariousArtist());
            stm.setString(5, album.getAlbumDescription());
            stm.setTime(6, Time.valueOf(album.getTotalTime().toString()));
            stm.setInt(7, album.getTracksCount().intValue());
            stm.setString(8, album.getAlbumCoverSRC());
            stm.setInt(9, album.getAlbumViews().intValue());
            stm.setDate(10, java.sql.Date.valueOf(album.getAlbumDateCreated().toString()));
            stm.executeUpdate();
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }
}
